﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using AutoTest.Common.Extension;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace AutoTest.Common.UtilsHelper
{

    /// <summary>
    /// Excel帮助类
    /// </summary>
    public class ExcelHelper
    {
        public const string XLSX_EXT = ".xlsx";
        /// <summary>
        /// 读取Excel，转成对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="stream"></param>
        /// <param name="fileName"></param>
        /// <param name="hasHeader"></param>
        /// <returns></returns>
        public static List<T> ReadFromExcel<T>(Stream stream, string fileName, bool hasHeader)
        {
            var ext = Path.GetExtension(fileName);
            var workbook = GetWorkBook(stream, string.Compare(ext, XLSX_EXT, true) == 0);
            var sheetIndex = 0;
            var startIndex = 0;
            var sheet = workbook.GetSheetAt(sheetIndex)!;

            IRow headerRow = null!;

            if (hasHeader)
            {
                startIndex = 1;
                headerRow = sheet?.GetRow(0)!;
            }

            var colList = GetColumns<T>(headerRow);
            var dataList = new List<T>();
            for (; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            {
                var rowIndex = startIndex;
                var row = sheet?.GetRow(rowIndex);

                while (row != null)
                {
                    var data = ExcelRowToOject<T>(row, colList);
                    if (data != null)
                    {
                        dataList.Add(data);
                    }
                    rowIndex++;
                    row = sheet?.GetRow(rowIndex);
                }

            }

            return dataList;
        }

        public static Stream ExportToExcel<T>(List<T> dataList, string sheetName = "data", int countPerSheet = 100000)
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            var colums = new List<ExportColumns>();
            foreach (var property in properties)
            {
                var col = new ExportColumns
                {
                    Property = property
                };
                var attribute = property.GetCustomAttribute<ExcelHeaderAttribute>();
                if (attribute != null)
                {
                    col.Sort = attribute.Sort;
                    col.ColName = attribute.Description;
                    col.Format = attribute.Format;
                    colums.Add(col);
                }
                //else
                //{
                //    col.Sort = 999;
                //    col.ColName = property.Name;
                //}
            }

            colums = [.. colums.OrderBy(t => t.Sort)];
            var workbook = GetWorkBook(true);

            int pageCount = (int)Math.Ceiling(dataList.Count * 1.0M / countPerSheet);
            for (int pageIndex = 0; pageIndex < pageCount; pageIndex++)
            {
                var rowIndex = 0;
                var pageData = dataList.Skip(pageIndex * countPerSheet).Take(countPerSheet);
                var pageSheetName = $"sheet-{(pageIndex + 1)}";
                if (sheetName.IsNotEmpty())
                {
                    pageSheetName = $"{sheetName}{(pageCount > 1 ? "-" + (pageIndex + 1) : "")}";
                }
                var sheet = workbook.CreateSheet(pageSheetName);
                #region 写表头
                var header = sheet.CreateRow(rowIndex++);
                int colIndex = 0;
                foreach (var col in colums)
                {
                    var cell = header.CreateCell(colIndex++);
                    cell.SetCellValue(col.ColName);
                    cell.SetCellType(CellType.String);
                }
                #endregion

                foreach (var data in pageData)
                {
                    var row = sheet.CreateRow(rowIndex++);
                    colIndex = 0;
                    foreach (var col in colums)
                    {
                        var cell = row.CreateCell(colIndex++);
                        var strValue = string.Empty;
                        if ((
                               col.Property?.PropertyType.FullName == typeof(DateTime).FullName
                            || col.Property?.PropertyType.FullName == typeof(int).FullName
                            || col.Property?.PropertyType.FullName == typeof(long).FullName
                            || col.Property?.PropertyType.FullName == typeof(short).FullName
                            || col.Property?.PropertyType.FullName == typeof(decimal).FullName
                            || col.Property?.PropertyType.FullName == typeof(float).FullName
                            || col.Property?.PropertyType.FullName == typeof(double).FullName
                            || col.Property?.PropertyType.FullName == typeof(DateTime?).FullName
                            || col.Property?.PropertyType.FullName == typeof(int?).FullName
                            || col.Property?.PropertyType.FullName == typeof(long?).FullName
                            || col.Property?.PropertyType.FullName == typeof(short?).FullName
                            || col.Property?.PropertyType.FullName == typeof(decimal?).FullName
                            || col.Property?.PropertyType.FullName == typeof(float?).FullName
                            || col.Property?.PropertyType.FullName == typeof(double?).FullName
                            ) && col.Format.IsNotEmpty())
                        {

                            strValue = (col.Property?.GetGetMethod()?.Invoke(data, null) as DateTime?)?.ToString(col.Format) ?? "";
                        }
                        else
                        {
                            strValue = col.Property?.GetGetMethod()?.Invoke(data, null)?.ToString() ?? "";
                        }
                        cell.SetCellValue(strValue);
                        cell.SetCellType(CellType.String);
                    }
                }
            }
            var stream = new MemoryStream();
            workbook.Write(stream, true);
            return stream;
        }
        class ExportColumns
        {
            /// <summary>
            /// 属性
            /// </summary>
            public PropertyInfo Property { get; set; } = null!;
            /// <summary>
            /// 排序
            /// </summary>
            public int Sort { get; set; }
            /// <summary>
            /// 列名字
            /// </summary>
            public string ColName { get; set; } = string.Empty;
            /// <summary>
            /// 
            /// </summary>
            public string Format { get; set; } = string.Empty;
        }
        private static List<ColumnInfo> GetColumns<T>(IRow headerRow)
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            var colList = new List<ColumnInfo>();
            foreach (var property in properties)
            {
                var attribute = property.GetCustomAttribute<ExcelHeaderAttribute>(false);
                var col = new ColumnInfo()
                {
                    Name = property.Name,
                    Description = attribute?.Description ?? property.Name,
                    ColIndex = -1,
                    Format = attribute?.Format ?? ""
                };
                colList.Add(col);
            }

            foreach (var cell in headerRow.Cells)
            {
                var strValue = cell.ToString();
                var col = colList.FirstOrDefault(t => t.Name == strValue || t.Description == strValue);
                if (col != null)
                {
                    col.ColIndex = cell.ColumnIndex;
                }
            }

            return colList;
        }

        private static T ExcelRowToOject<T>(IRow row, List<ColumnInfo> columnList)
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            var instance = Activator.CreateInstance(type);
            foreach (var property in properties)
            {
                var col = columnList.FirstOrDefault(t => t.Name == property.Name);
                if (col == null) continue;
                if (col.ColIndex < 0) continue;

                var cell = row.GetCell(col.ColIndex);
                if (cell == null) continue;

                var strValue = (cell?.ToString() ?? "").Trim();
                if (string.IsNullOrWhiteSpace(strValue)) continue;

                object value = null!;
                if (property.PropertyType.IsValueType)
                {
                    value = Convert.ChangeType(strValue, property.PropertyType);
                }
                else if (property.PropertyType.FullName == typeof(string).FullName)
                {
                    value = strValue;
                }
                else { }
                if (value == null) { continue; }
                property.SetValue(instance, value);

            }
            return (T)instance!;
        }

        private static IWorkbook GetWorkBook(Stream stream, bool isXlsx) => isXlsx ? new XSSFWorkbook(stream) : new HSSFWorkbook(stream);
        private static IWorkbook GetWorkBook(bool isXlsx) => isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    }

    class ColumnInfo
    {
        public string Name { get; set; } = string.Empty;
        public string Description { get; set; } = string.Empty;
        public int ColIndex { get; set; }
        public string Format { get; set; } = string.Empty;
    }

    /// <summary>
    /// 
    /// </summary>
    [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
    public class ExcelHeaderAttribute : Attribute
    {
        /// <summary>
        /// 描述信息
        /// </summary>
        public string Description { get; set; } = string.Empty;
        /// <summary>
        /// 排序，导出时使用
        /// </summary>
        public int Sort { get; set; }
        /// <summary>
        /// 时间和数字格式化
        /// </summary>
        public string Format { get; set; } = "";
    }
}
